package com.situ.mvc.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.situ.mvc.dao.IBanjiDao;
import com.situ.mvc.util.JDBCUtil;
import com.situ.mvc.util.ModelConvertUtil;
import com.situ.mvc.entity.Banji;

public class BanjiDaoImpl implements IBanjiDao {
	
	/*列表展示*/
	@Override
	public List<Banji> list() {
		Connection connection = null; //放在外面
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<Banji> list = new ArrayList<>();
		try {
			//连接数据库执行sql语句
			connection = JDBCUtil.getConnection();
			String sql = "SELECT id ,NAME,people,teatch FROM banji";
			//预编译
			preparedStatement = connection.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {//下一条数据，有就拿出来
			Integer id = resultSet.getInt("id");
			String name = resultSet.getString("name");
			Integer people = resultSet.getInt("people");
			String teatch = resultSet.getString("teatch");
			Banji banji = new Banji(id, name, people, teatch);//new班级对象
			list.add(banji); //把班级信息放入list数组
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(connection, preparedStatement, resultSet);
		}
		
		return list;
	}
	
	/*根据id删除*/
	@Override
	public int deleteById(Integer id) {
		Connection connection = null; //放在外面
		PreparedStatement preparedStatement = null;
		int count = 0;
		try {
			//连接数据库执行sql语句
			connection = JDBCUtil.getConnection();
			String sql = "delete from banji where id=?";
			//预编译
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, id);
			count = preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(connection, preparedStatement, null);
		}
		return count;
	}
	
	/*添加数据*/
	@Override
	public int insert(Banji banji) {
		Connection connection = null; //放在外面
		PreparedStatement preparedStatement = null;
		int count = 0;
		try {
			//连接数据库执行sql语句
			connection = JDBCUtil.getConnection();
			String sql = "insert into banji(name,people,teatch,course_id) values(?,?,?,?)";
			//预编译
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, banji.getName());
			preparedStatement.setInt(2, banji.getPeople());
			preparedStatement.setString(3, banji.getTeatch());
			preparedStatement.setInt(4, banji.getCourseId());
			count = preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(connection, preparedStatement, null);
		}
		return count;
	}
	
	/*修改数据前展示数据*/
	@Override
	public Banji findById(Integer id) {
		Connection connection = null; //放在外面
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		Banji banji = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT b.id AS b_id ,b.name AS b_name,b.people AS b_people,b.teatch AS b_teatch,c.name AS c_name FROM banji AS b INNER JOIN course AS  c ON b.course_id = c.id WHERE b.id = ?";
			// 预编译sql
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, id);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				Integer bjId = resultSet.getInt("b_id");//重复
				String bjName = resultSet.getString("b_name");
				Integer people = resultSet.getInt("b_people");
				String teatch = resultSet.getString("b_teatch");
				String courseName = resultSet.getString("c_name");
				banji = new Banji(bjId, bjName, people, teatch, null, courseName);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(connection, preparedStatement, resultSet);
		}
		return banji;
	}
	
	/*修改*/
	@Override
	public int update(Banji banji) {
		Connection connection = null; //放在外面
		PreparedStatement preparedStatement = null;
		int count = 0;
		try {
			//连接数据库执行sql语句
			connection = JDBCUtil.getConnection();
			String sql = "update banji set name=?,people=?,teatch=?,course_id=? where id=?";
			//预编译
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, banji.getName());
			preparedStatement.setInt(2, banji.getPeople());
			preparedStatement.setString(3, banji.getTeatch());
			preparedStatement.setInt(4, banji.getCourseId());
			preparedStatement.setInt(5, banji.getId());
			count = preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(connection, preparedStatement, null);
		}
		return count;
	}
	
	/*批量删除*/
	@Override
	public int deleteAll(String[] selectIds) {
		Connection connection = null; //放在外面
		PreparedStatement preparedStatement = null;
		int count = 0;
		try {
			//连接数据库执行sql语句
			connection = JDBCUtil.getConnection();
			String sql = "delete from banji where id=?";
			//预编译
			preparedStatement = connection.prepareStatement(sql);
			for (String id : selectIds) {
				preparedStatement.setInt(1, Integer.parseInt(id));
				preparedStatement.addBatch();
			}
			int[] counts = preparedStatement.executeBatch();
			count = counts.length;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(connection, preparedStatement, null);
		}
		return count;
	}

	/*总的记录数*/
	@Override
	public int getTotalCount() {
		Connection connection = null; //放在外面
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		int count = 0;
		try {
			//连接数据库执行sql语句
			connection = JDBCUtil.getConnection();
			String sql = "SELECT count(id) FROM banji";
			//预编译
			preparedStatement = connection.prepareStatement(sql);
			System.out.println(preparedStatement);
			resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				count = resultSet.getInt(1);//第一列
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(connection, preparedStatement, null);
		}
		return count;
	}
	
	//分页的数据
	//@Override
	public List<Banji> pageList1(int offset, int pageSize) {
		Connection connection = null; //放在外面
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<Banji> list = new ArrayList<>();
		try {
			//连接数据库执行sql语句
			connection = JDBCUtil.getConnection();
			String sql = "SELECT id,name,people,teatch FROM banji limit ?,?";
			//预编译
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, offset);
			preparedStatement.setInt(2, pageSize);
			System.out.println(preparedStatement);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				Integer id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				Integer people = resultSet.getInt("people");
				String teatch = resultSet.getString("teatch");
				Banji banji = new Banji(id, name, people, teatch);
				list.add(banji);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(connection, preparedStatement, null);
		}
		return list;
	}
	
	@Override
	public List<Map<String, Object>> pageList(int offset, int pageSize) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<Map<String, Object>> list = new ArrayList<>();
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT b.id AS b_id ,b.name AS b_name,b.people AS b_people,b.teatch AS b_teatch,c.name AS c_name FROM banji AS b INNER JOIN course AS  c ON b.course_id = c.id LIMIT ?,?;";
			// 预编译sql
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, offset);
			preparedStatement.setInt(2, pageSize);
			System.out.println(preparedStatement);
			resultSet = preparedStatement.executeQuery();
			list = ModelConvertUtil.converToList(resultSet);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, resultSet);
		}
		
		return list;
	}
	
	//添加班级避免重复
	@Override
	public int selectCountByName(String name) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		int count = 0;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT count(id) from	banji where name=?";
			// 预编译sql
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, name);
			
			resultSet = preparedStatement.executeQuery();
				if (resultSet.next()) {
					count = resultSet.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, resultSet);
		}
		
		return count;
	}

}
